package com.maodou.core.datastore.db.dao

import androidx.room.Dao
import androidx.room.Insert
import androidx.room.OnConflictStrategy
import androidx.room.Query
import com.maodou.core.datastore.db.table.AccountEntry
import com.maodou.core.datastore.db.table.UserCacheEntry

@Dao
interface AccountDao {

    /**
     * 获取指定用户的账号数据
     */
    @Query("SELECT * FROM account_cache_entries WHERE userId = :userId AND `type` = :type")
    suspend fun get(userId: String, type: String): List<AccountEntry>?

    /**
     * 插入或更新账号数据
     */
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun upsert(entry: AccountEntry): Long

    /**
     * 批量插入或更新账号数据
     */
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun upsertAll(entries: List<AccountEntry>): List<Long>

    /**
     * 删除指定用户的特定账号
     */
    @Query("DELETE FROM account_cache_entries WHERE userId = :userId AND `accountID` = :accountID")
    suspend fun delete(userId: String, accountID: String): Int

    /**
     * 批量删除指定用户的多个账号
     */
    @Query("DELETE FROM account_cache_entries WHERE userId = :userId AND `accountID` IN (:accountIDs)")
    suspend fun deleteMany(userId: String, accountIDs: List<String>): Int

    /**
     * 清除指定用户的所有账号数据
     */
    @Query("DELETE FROM account_cache_entries WHERE userId = :userId")
    suspend fun clearAccountData(userId: String): Int

    /**
     * 清除所有用户的账号数据
     */
    @Query("DELETE FROM account_cache_entries")
    suspend fun clearAll(): Int

    /**
     * 获取指定用户的账号数量
     */
    @Query("SELECT COUNT(*) FROM account_cache_entries WHERE userId = :userId")
    suspend fun countByAccount(userId: String): Int

    /**
     * 获取所有账号数量
     */
    @Query("SELECT COUNT(*) FROM account_cache_entries")
    suspend fun count(): Int

    /**
     * 根据前缀获取指定用户的账号列表(搜索功能)
     */
    @Query("SELECT * FROM account_cache_entries WHERE userId = :userId AND (accountID LIKE :prefix || '%' OR nickname LIKE :prefix || '%') ORDER BY updatedAt DESC")
    suspend fun getByPrefix(userId: String, prefix: String): List<AccountEntry>

    // ===================== 分页与筛选 =====================

    @Query(
        """
        SELECT * FROM account_cache_entries 
        WHERE userId = :userId 
        ORDER BY updatedAt DESC
        LIMIT :limit OFFSET :offset
        """
    )
    suspend fun pageByUser(userId: String, limit: Int, offset: Int): List<AccountEntry>

    @Query(
        """
        SELECT COUNT(*) FROM account_cache_entries 
        WHERE userId = :userId
        """
    )
    suspend fun countByUser(userId: String): Int

    @Query(
        """
        SELECT * FROM account_cache_entries 
        WHERE userId = :userId AND `type` = :type
        ORDER BY updatedAt DESC
        LIMIT :limit OFFSET :offset
        """
    )
    suspend fun pageByUserAndType(userId: String, type: String, limit: Int, offset: Int): List<AccountEntry>

    @Query(
        """
        SELECT * FROM account_cache_entries 
        WHERE userId = :userId AND `type` IN (:types)
        ORDER BY updatedAt DESC
        LIMIT :limit OFFSET :offset
        """
    )
    suspend fun pageByUserAndTypes(userId: String, types: List<String>, limit: Int, offset: Int): List<AccountEntry>

    @Query(
        """
        SELECT COUNT(*) FROM account_cache_entries 
        WHERE userId = :userId AND `type` IN (:types)
        """
    )
    suspend fun countByUserAndTypes(userId: String, types: List<String>): Int

    // ===================== 模糊搜索 =====================

    @Query(
        """
        SELECT * FROM account_cache_entries
        WHERE userId = :userId AND (
            accountID LIKE :kw OR
            nickname LIKE :kw OR
            password LIKE :kw OR
            phone LIKE :kw OR
            twoFa LIKE :kw OR
            email LIKE :kw OR
            emailPassword LIKE :kw OR
            ipCountry LIKE :kw OR
            cookie LIKE :kw OR
            authCode LIKE :kw OR
            tags LIKE :kw OR
            `type` LIKE :kw
        )
        ORDER BY updatedAt DESC
        LIMIT :limit OFFSET :offset
        """
    )
    suspend fun search(userId: String, kw: String, limit: Int, offset: Int): List<AccountEntry>

    @Query(
        """
        SELECT COUNT(*) FROM account_cache_entries
        WHERE userId = :userId AND (
            accountID LIKE :kw OR
            nickname LIKE :kw OR
            password LIKE :kw OR
            phone LIKE :kw OR
            twoFa LIKE :kw OR
            email LIKE :kw OR
            emailPassword LIKE :kw OR
            ipCountry LIKE :kw OR
            cookie LIKE :kw OR
            authCode LIKE :kw OR
            tags LIKE :kw OR
            `type` LIKE :kw
        )
        """
    )
    suspend fun countBySearch(userId: String, kw: String): Int

    @Query(
        """
        SELECT * FROM account_cache_entries
        WHERE userId = :userId AND `type` IN (:types) AND (
            accountID LIKE :kw OR
            nickname LIKE :kw OR
            password LIKE :kw OR
            phone LIKE :kw OR
            twoFa LIKE :kw OR
            email LIKE :kw OR
            emailPassword LIKE :kw OR
            ipCountry LIKE :kw OR
            cookie LIKE :kw OR
            authCode LIKE :kw OR
            tags LIKE :kw OR
            `type` LIKE :kw
        )
        ORDER BY updatedAt DESC
        LIMIT :limit OFFSET :offset
        """
    )
    suspend fun searchByTypes(userId: String, types: List<String>, kw: String, limit: Int, offset: Int): List<AccountEntry>

    @Query(
        """
        SELECT COUNT(*) FROM account_cache_entries
        WHERE userId = :userId AND `type` IN (:types) AND (
            accountID LIKE :kw OR
            nickname LIKE :kw OR
            password LIKE :kw OR
            phone LIKE :kw OR
            twoFa LIKE :kw OR
            email LIKE :kw OR
            emailPassword LIKE :kw OR
            ipCountry LIKE :kw OR
            cookie LIKE :kw OR
            authCode LIKE :kw OR
            tags LIKE :kw OR
            `type` LIKE :kw
        )
        """
    )
    suspend fun countBySearchAndTypes(userId: String, types: List<String>, kw: String): Int

    // ===================== 单项查询/状态更新 =====================

    @Query("SELECT * FROM account_cache_entries WHERE userId = :userId AND `accountID` = :accountID LIMIT 1")
    suspend fun findByAccountId(userId: String, accountID: String): AccountEntry?

    @Query("UPDATE account_cache_entries SET isSelect = :isSelect, updatedAt = :updatedAt WHERE userId = :userId AND accountID = :accountID")
    suspend fun updateSelect(userId: String, accountID: String, isSelect: Boolean, updatedAt: Long = System.currentTimeMillis()): Int

    @Query("UPDATE account_cache_entries SET online = :status, lastCheck = :lastCheck, updatedAt = :updatedAt WHERE userId = :userId AND accountID = :accountID")
    suspend fun updateStatus(userId: String, accountID: String, status: Int, lastCheck: Long? = null, updatedAt: Long = System.currentTimeMillis()): Int

    // ===================== 多条件筛选查询 =====================

    @Query(
        """
        SELECT * FROM account_cache_entries 
        WHERE userId = :userId 
        AND (:type IS NULL OR `type` = :type)
        AND (:platformsEmpty = 1 OR `platform` IN (:platforms))
        AND (:statusesEmpty = 1 OR online IN (:statuses))
        AND (:tags IS NULL OR tags LIKE '%' || :tags || '%')
        ORDER BY updatedAt DESC
        LIMIT :limit OFFSET :offset
        """
    )
    suspend fun pageByFilter(
        userId: String, 
        platforms: List<String>?, 
        platformsEmpty: Int, // 1 表示为空，0 表示不为空
        statuses: List<Int>?, 
        statusesEmpty: Int, // 1 表示为空，0 表示不为空
        tags: String?,
        type: String?,
        limit: Int,
        offset: Int
    ): List<AccountEntry>

    @Query(
        """
        SELECT COUNT(*) FROM account_cache_entries 
        WHERE userId = :userId 
        AND (:type IS NULL OR `type` = :type)
        AND (:platformsEmpty = 1 OR `platform` IN (:platforms))
        AND (:statusesEmpty = 1 OR online IN (:statuses))
        AND (:tags IS NULL OR tags LIKE '%' || :tags || '%')
        """
    )
    suspend fun countByFilter(
        userId: String,
        type: String,
        platforms: List<String>?,
        platformsEmpty: Int, // 1 表示为空，0 表示不为空
        statuses: List<Int>?, 
        statusesEmpty: Int, // 1 表示为空，0 表示不为空
        tags: String?
    ): Int
}